package com.scalar.chiptrack.operations.process.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Iterator;
import java.util.HashMap;

import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.SQLException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;

import com.scalar.chiptrack.operations.process.VendorLocation;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.operations.vendor.dao.VendorDAO;
import com.scalar.chiptrack.operations.process.ProcessStep;

/**
** VendorLocationDAO interacts with ProcessStep_Location_Table in the database
** @author Mallikarjun Ankati
**
** @param 0
**/
public class VendorLocationDAO {
	
	private VendorLocationDAO()
	{
	}

       
	/**
	** Load the data into the Process_Step_Location_Table
	** 
	**/

	public static void saveVendorLocations (DBTransaction dbTransaction, int processStepID, Iterator vendorLocationList) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;

		if (vendorLocationList == null)
		{
			return;
		}
                
		try
		{
			// Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
                
			pstmt = conn.prepareStatement(SqlQueryMappings.get("operations.process.vendorLocation.insertLocations"));

			VendorLocation vendorLocation = null;

			while (vendorLocationList.hasNext())
			{
				vendorLocation = (VendorLocation)vendorLocationList.next();
				pstmt.setInt(1, processStepID);
				pstmt.setInt(2, vendorLocation.getLocationIDAsInt());
				pstmt.setInt(3, vendorLocation.getPriorityAsInt());
				//pstmt.setInt(4, vendorLocation.getSplitAsInt());
				//System.out.println( vendorLocation.getSplit() );
				if( vendorLocation.getSplit() != null )
				pstmt.setFloat( 4 , Float.parseFloat( vendorLocation.getSplit() ) );
				else
				pstmt.setFloat( 4 , 0.0f );
				pstmt.setInt(5, vendorLocation.getCapacityWeekAsInt());
				pstmt.setFloat(6, vendorLocation.getNormalCost());
				pstmt.setFloat(7, vendorLocation.getExpressCost());
				pstmt.setInt(8, vendorLocation.getNormalCycleTimeAsInt());
				pstmt.setInt(9, vendorLocation.getExpressCycleTimeAsInt());
				pstmt.setFloat(10, vendorLocation.getExpectedYield());
				pstmt.setInt(11, (vendorLocation.getRawMaterialSource()?0:1));
				pstmt.setInt(12, vendorLocation.getRawLevelMinAsInt());
				pstmt.setInt(13, vendorLocation.getRawLevelMaxAsInt());
				pstmt.setInt(14, (vendorLocation.getLaserRepairAllowed()?0:1));
				pstmt.setFloat(15, vendorLocation.getLaserRepairCost());

				pstmt.addBatch();
			}
			pstmt.executeBatch();
		}
		catch(Exception sql)
		{
			sql.printStackTrace();
			throw new ScalarException("Could not access VendorLocationList", sql);
		}
		finally
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null)
					{
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}

			if(pstmt != null)
			{
				try{pstmt.close();}catch(SQLException se){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException se){}
			}			
		}
	}

	/**
	**	returns the ArrayList of VendorLocation objects
	**	@param processStepID
	**	@return ArrayList
	**/
	public static ArrayList getVendorLocations(int processStepID) throws ScalarException
	{
		Connection conn = null;	
		PreparedStatement ps = null;
		ResultSet rs = null;
		ArrayList vLocationList = new ArrayList();

		try
		{
			conn = DataSource.getConnection();
			ps = conn.prepareStatement(SqlQueryMappings.get("operations.process.vendorLocation.readLocations"));
			VendorLocation vLocation = null;
			ps.setInt(1, processStepID);

			rs = ps.executeQuery();

			while(rs.next())
			{
				vLocation = new VendorLocation();
				vLocation.setLocationID( rs.getInt("Location_ID")+"" );
				vLocation.setLocationName(rs.getString("L_Vendor_Name") + "/" + rs.getString("City"));
				vLocation.setPriority(rs.getInt ("PSLT_Priority")+"" );
				vLocation.setSplit( rs.getInt("PSLT_Split")+"" );
				vLocation.setCapacityWeek( rs.getInt("PSLT_Capacity")+"" );
				vLocation.setNormalCost( rs.getFloat("PSLT_Cost_Normal") );
				vLocation.setExpressCost( rs.getFloat("PSLT_Cost_Express") );
				vLocation.setNormalCycleTime( rs.getInt("PSLT_Cycle_Time_Normal")+"" );
				vLocation.setExpressCycleTime( rs.getInt("PSLT_Cycle_Time_Express")+"" );
				vLocation.setExpectedYield( rs.getFloat("PSLT_Expected_Yield") );
				vLocation.setRawMaterialSource( ((rs.getInt("PSLT_Asy_Raw_Material_Supply") == 0)?true:false) );
				vLocation.setRawLevelMin( rs.getInt("PSLT_Asy_Min_Level")+"" );
				vLocation.setRawLevelMax( rs.getInt("PSLT_Asy_Max_Level")+"" );
				vLocation.setLaserRepairAllowed( ((rs.getInt("PSLT_Laser_Repaired") == 0)?true:false) );
				vLocation.setLaserRepairCost( rs.getFloat("PSLT_Laser_Repaired_Cost") );

				vLocationList.add(vLocation);
			}
			
		}
		catch(SQLException sql)
		{
			sql.printStackTrace();
			throw new ScalarException("Reading Vendor Location failed ", sql);
		}
		finally
		{
			if(ps != null)
			{
				try{ ps.close(); }catch(SQLException se){}
			}
			if(conn != null)
			{
				try{   conn.close();   }catch(SQLException se){}
			}
		}
		return vLocationList;
	}

	public static ArrayList getAllVendorLocations(int processStepId) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList locations = null;
		VendorLocation vLocation = null;
		ArrayList vendorLocations = new ArrayList();

		try
		{
			conn = DataSource.getConnection();

			locations = VendorDAO.getTestLocations();
			String processType = ProcessStep.getProcessType(processStepId);
			pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.selectLocaitonByStepId"));
			for(int i=0; i<locations.size(); i++)
			{
				vLocation = (VendorLocation) locations.get(i);
				int locationId = vLocation.getLocationIDAsInt();

				pstmt.setInt(1,locationId);
				pstmt.setInt(2,processStepId);
				pstmt.setString(3, processType);

				rs = pstmt.executeQuery();    

				if(rs.next())
				{
					vLocation = new VendorLocation();
					vLocation.setLocationID( rs.getInt("Location_ID")+"" );
					vLocation.setSplit( rs.getInt("PSLT_Split")+"" );
					vLocation.setCapacityWeek( rs.getInt("PSLT_Capacity")+"" );
					vLocation.setNormalCost( rs.getFloat("PSLT_Cost_Normal") );
					vLocation.setExpressCost( rs.getFloat("PSLT_Cost_Express") );
					//vLocation.setNormalCycleTime( rs.getInt("PSLT_Cycle_Time_Normal") );
					//vLocation.setExpressCycleTime( rs.getInt("PSLT_Cycle_Time_Express") );
					vLocation.setExpectedYield( rs.getFloat("PSLT_Expected_Yield") );
					vLocation.setRawMaterialSource( ((rs.getInt("PSLT_Asy_Raw_Material_Supply") == 0)?true:false) );
					vLocation.setRawLevelMin( rs.getInt("PSLT_Asy_Min_Level")+"" );
					vLocation.setRawLevelMax( rs.getInt("PSLT_Asy_Max_Level")+"" );
					vLocation.setLaserRepairAllowed( ((rs.getInt("PSLT_Laser_Repaired") == 0)?true:false) );
					vLocation.setLaserRepairCost( rs.getFloat("PSLT_Laser_Repaired_Cost") );

					vendorLocations.add(vLocation);
				}
				else
				{
				vendorLocations.add(vLocation);
				}
			}
		}
		catch (Exception sqlex)
		{
			sqlex.printStackTrace();
			throw new ScalarException ("Could not get vendors from database", sqlex);
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException excp){}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException excp){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException excp){}
			}
		}
		return vendorLocations;
	}

	//Testing purpose only

	public static void main(String[] args)
	{
		/*VendorLocation vendorLocation = new VendorLocation();
		vendorLocation.setLocationID(3);
		vendorLocation.setSplit(50);
		vendorLocation.setCapacityWeek(60);
		vendorLocation.setNormalCost(50.5);
		vendorLocation.setExpressCost(101.2);
		vendorLocation.setNormalCycleTime(200);
		vendorLocation.setExpressCycleTime(300);
		vendorLocation.setExpectedYield(90.9);
		vendorLocation.setRawMaterialSource(true);
		vendorLocation.setRawLevelMin(50);
		vendorLocation.setRawLevelMax(100);
		vendorLocation.setLaserRepairAllowed(false);
		vendorLocation.setLaserRepairCost(0.0);
		List list = new ArrayList();
		list.add(vendorLocation);

		try{
		saveVendorLocations(1001, list.iterator());
		}catch(Exception e){}
		*/
		try
		{
			List list = null;
			list = getVendorLocations(1001);
			Iterator it = list.iterator();

			VendorLocation vendorLocation = null;

			while(it.hasNext())
			{
				vendorLocation = (VendorLocation)it.next();

				System.out.println("Location = "+vendorLocation.getLocationID());
				System.out.println("Split = "+vendorLocation.getSplit());
				System.out.println("Capacity = "+vendorLocation.getCapacityWeek());
				System.out.println("Normal Cost = "+vendorLocation.getNormalCost());
				System.out.println("Express Cost ="+vendorLocation.getExpressCost());
				System.out.println("Normal Cycle = "+vendorLocation.getNormalCycleTime());
			}
		}
		catch ( Exception e ){}
	}

	//Delete Vendor Locations 

	public static void deleteVendorLocations ( DBTransaction dbTransaction, int processStepID ) throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean status = false;

		try
		{
			// Get the connection from regular datasource if its not in a manual transaction
			if (dbTransaction == null)
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

			pstmt = conn.prepareStatement (SqlQueryMappings.get ("vendor.deleteVendorLocations"));
			pstmt.setInt(1,processStepID);
			pstmt.executeUpdate();
			status = true;
		}

		catch(Exception sql)
		{
			sql.printStackTrace();
			throw new ScalarException("Could not Delete Vendor Locations ", sql);
		}

		finally
		{
			// Commit/Rollback here it selef -- if its not in a manual transaction
			if (dbTransaction == null)
			{
				try
				{
					if (conn != null) {
						if (status == true)
						{
							conn.commit();
						}
						else
						{
							conn.rollback();
						}
					}
				}
				catch (SQLException ignore) {}
			}
			if(pstmt != null)
			{
				try{pstmt.close();}catch(SQLException se){}
			}
			if(conn != null)
			{
				try{ conn.close(); }catch(SQLException se){}
			}			
		}
	}


	public static void getLocationsOfRunningLots ( DBTransaction dbTransaction, int processStepId, ArrayList location, HashMap errors ) 
	throws ScalarException
	{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList locations = new ArrayList();
		VendorLocation vLocation = null;
		ArrayList vendorLocations = new ArrayList();
		ArrayList tempLocationName = new ArrayList();
		String errMsg = "";

		try
		{
			if ( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}
			pstmt = conn.prepareStatement ( SqlQueryMappings.get ( "operations.boards.getLocationsOfRunningLots" ) );
			pstmt.setInt ( 1, processStepId );
			pstmt.setInt ( 2, processStepId );
			rs = pstmt.executeQuery();
			
			while ( rs.next() )
			{
				vLocation = new VendorLocation();				
				vLocation.setLocationID ( rs.getInt( "location_id" ) + "" );
				vLocation.setLocationName ( rs.getString ( "l_vendor_name" ) );
				locations.add ( vLocation );
				
			}
			if ( locations != null && locations.size () > 0 )
			{
				for ( int count1 = 0; count1 < locations.size(); count1++ )
				{
					String locationId = ( ( VendorLocation ) locations.get ( count1 ) ).getLocationID ().trim();
					String locationName = ( ( VendorLocation ) locations.get ( count1 ) ).getLocationName ().trim();
					boolean exists = false;
					for ( int count2 = 0; count2 < location.size(); count2++ )
					{
						if ( locationId.equalsIgnoreCase ( ( ( VendorLocation )location.get(count2)).getLocationID().trim() ) )
						{
							exists = true;
						}
					}
					if ( !exists )
					{
						tempLocationName.add ( locationName );
					}
				}
				if ( tempLocationName != null && tempLocationName.size() > 0 )
				{
					for ( int count3 = 0; count3 < tempLocationName.size(); count3++ )
					{
						errMsg += " "+tempLocationName.get ( count3 ).toString()+",";
					}
					if ( errMsg.length() > 0 )
					{
						errMsg = errMsg.substring (0, errMsg.length ()-1 );
					}
					throw new ScalarException ();
				}
			}
		}
		catch ( ScalarException se )
		{
			if ( tempLocationName.size() == 1 )
			{
				errors.put (errMsg+" cannot be deselected because it has running lots", null );
			}
			else if ( tempLocationName.size() > 1 )
			{
				errors.put (errMsg+" cannot be deselected because they have running lots", null );
			}
			throw new ScalarException ( tempLocationName+" cannot be deselected because it has running lots" );
			
		}
		catch (Exception sqlex)
		{
			//sqlex.printStackTrace();
			errors.put ( "Could not get Vendor locations from database", null );
			throw new ScalarException ("Could not get Vendor locations from database", sqlex);
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException excp){}
			}
			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException excp){}
			}
			if (conn != null)
			{
				try { conn.close(); } catch (SQLException excp){}
			}
		}
	} 
	//Ends Here
}